﻿using AutoMapper;
using DFGK_WMS.Context;
using DFGK_WMS.IRepository;
using DFGK_WMS.Model.DTO;
using DFGK_WMS.Model.Models;
using DFGK_WMS.Model.NoNet;
using Microsoft.Data.Sqlite;
using Microsoft.VisualBasic;
using Newtonsoft.Json;
using NPOI.XWPF.UserModel;
using Org.BouncyCastle.Asn1.X509;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;

namespace DFGK_WMS.Repository
{
    /// <summary>
    /// 盘点单管理
    /// </summary>
    public class TStocktakingOrderRepository : BaseRepository<TStocktakingOrder>, ITStocktakingOrderRepository
    {
        private readonly SqliteContext sqlite;
        public TStocktakingOrderRepository(DFGK_DB context, IMapper mapper, SqliteContext sqlite) : base(context, mapper)
        {
            this.sqlite = sqlite;

        }
        /// <summary>
        /// 导出盘点单号
        /// </summary>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public List<TStocktakingOrder> GetStockingExcel()
        {
            try
            {
                #region 弃用
                //var alist = Context.TStocktakingOrders.AsQueryable();//盘点单
                //var blist=Context.TMaterials.AsQueryable();//物料
                //var clist=Context.TAreas.AsQueryable();//库区
                //var dlist=Context.TWarehouses.AsQueryable();//库房
                //var query = from a in alist
                //            join b in blist
                //            on a.MaterialId equals b.Id
                //            join c in clist
                //            on a.AreaId equals c.Id
                //            join d in dlist
                //            on a.WarehouseId equals d.Id
                //            select new TStocktakingOrderDto
                //            {
                //                Name=a.Name,
                //                FName=d.Name,
                //                KName=c.Name,
                //                WName=b.Name,
                //                BillType=a.BillType,
                //                PlanType=a.PlanType,
                //                StocktakeWay= a.StocktakeWay,
                //                BatchNo = a.BatchNo,
                //                CreateUserName = a.CreateUserName,
                //                CreateDate = a.CreateDate
                //            };
                //return query.ToList();
                #endregion
                var slist = Context.TStocktakingOrders.ToList();
                return slist;
            }
            catch (Exception)
            {

                throw;
            }
        }
        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public bool AddStocktakingOrder(TStocktakingOrderDto dto, out string message)
        {
            using (var tran = Context.Database.BeginTransaction())
            {
                message = "";
                try
                {
                    TStocktakingOrder order = new TStocktakingOrder();
                    order = Mapper.Map<TStocktakingOrder>(dto);
                    //库房
                    //var wobj = Context.TWarehouses.Find(dto.WarehouseId);
                    //if (wobj != null && wobj.Status == 1)
                    //{
                    //    order.WarehouseName = wobj.Name;
                    //    order.WarehouseCode = wobj.Code;
                    //}
                    //else
                    //{
                    //    message = "库房不存在或已被禁用！";
                    //    return false;
                    //}
                    //状态默认为创建
                    order.Status = 0;
                    //盘点计划默认为普通抽盘
                    order.PlanType = 10;
                    //订单明细的数量
                    order.FinishedCount = dto.Inventorys.Count;
                    order.CreateDate = DateTime.Now;
                    order.CreateUserId = dto.NowUserId;
                    order.ReasonName = dto.Reason;
                    order.CreateUserName = dto.NowUserRealName;

                    Context.TStocktakingOrders.Add(order);
                    Context.SaveChanges();

                    foreach (var item in dto.Inventorys)
                    {
                        TStocktakingOrderDetail m = new TStocktakingOrderDetail();
                        m.OrderId = order.Id;
                        m.OrderSn = order.OrderSn;
                        m.CellId = item.LocationId;
                        m.CellCode = item.LocationCode;
                        m.LocationName = item.LocationName;
                        m.CellDepthIndex = item.LocationDepth;
                        m.PalletBarcode = item.PalletBarcode;
                        //m.Status = (int)item.Status;
                        m.MaterialId = item.MaterialId;
                        m.MaterialName = item.MaterialName;
                        m.MaterialCode = item.MaterialCode;
                        m.MaterialCategoryName = item.MaterialCategoryName;
                        m.CreateDate = DateTime.Now;
                        m.HasSkuDetail = item.HasSkuDetail;
                        m.Qty = item.AvailableQty;
                        m.QualityState = item.QualityState;


                        m.ExpireDate = item.ExpireDate;
                        m.ProductDate = item.ProductDate;
                        m.SupplierId = item.SupplierId;
                        m.SupplierName = item.SupplierName;
                        m.MaterialTypeId = item.MaterialTypeId;
                        m.MaterialTypeName = item.MaterialTypeName;
                        m.InventoryId = item.Id;
                        m.WarehouseId = item.WarehouseId;
                        Context.TStocktakingOrderDetails.Add(m);
                        Context.SaveChanges();
                    }
                    tran.Commit();

                    var stocktakingPlanList = Context.TStocktakingPlanRecord.ToList();
                    foreach (var item in stocktakingPlanList)
                    {
                        if(item.NowTime.ToString().Substring(0,9) == order.CreateDate.ToString().Substring(0, 9))
                        {
                            item.Counts = item.Counts + 1;
                            Context.TStocktakingPlanRecord.Update(item);
                            break;
                        }

                    }


                    return true;
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    throw ex;
                }
            }
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public bool UpdateStocktakingOrder(TStocktakingOrderDto dto, out string message)
        {
            using (var tran = Context.Database.BeginTransaction())
            {
                message = "";
                try
                {
                    TStocktakingOrder order = new TStocktakingOrder();
                    order = Mapper.Map<TStocktakingOrder>(dto);
                    //库房
                    //var wobj = Context.TWarehouses.Find(dto.WarehouseId);
                    //if (wobj != null && wobj.Status == 1)
                    //{
                    //    order.WarehouseName = wobj.Name;
                    //    order.WarehouseCode = wobj.Code;
                    //}
                    //else
                    //{
                    //    message = "库房不存在或已被禁用！";
                    //    return false;
                    //}

                    order.CreateDate = dto.CreateDate;
                    order.CreateUserId = dto.CreateUserId;
                    order.CreateUserName = dto.CreateUserName;
                    order.FinishedCount = dto.Inventorys.Count;
                    order.ModifyDate = DateTime.Now;
                    order.ReasonName = dto.Reason;
                    order.ModifyUserId = dto.NowUserId;
                    order.ModifyUserName = dto.NowUserRealName;
                    Context.TStocktakingOrders.Update(order);
                    Context.SaveChanges();

                    var slist = Context.TStocktakingOrderDetails.Where(x => x.OrderId == order.Id && x.OrderSn == order.OrderSn);
                    if (slist != null || slist.Any())
                    {
                        Context.TStocktakingOrderDetails.RemoveRange(slist);
                        Context.SaveChanges();
                    }
                    foreach (var item in dto.Inventorys)
                    {
                        TStocktakingOrderDetail m = new TStocktakingOrderDetail();
                        m.OrderId = order.Id;
                        m.OrderSn = order.OrderSn;
                        m.CellId = item.LocationId;
                        m.CellCode = item.LocationCode;
                        m.CellDepthIndex = item.LocationDepth;
                        m.PalletBarcode = item.PalletBarcode;
                        m.Status = (int)item.Status;
                        m.MaterialId = item.MaterialId;
                        m.MaterialName = item.MaterialName;
                        m.MaterialCode = item.MaterialCode;
                        m.CreateDate = DateTime.Now;
                        m.HasSkuDetail = item.HasSkuDetail;
                        m.Qty = item.AvailableQty;
                        m.QualityState = item.QualityState;
                        m.MaterialCategoryName = item.MaterialCategoryName;
                        m.LocationName = item.LocationName;
                        m.ExpireDate = item.ExpireDate;
                        m.ProductDate = item.ProductDate;
                        m.SupplierId = item.SupplierId;
                        m.SupplierName = item.SupplierName;
                        m.MaterialTypeId = item.MaterialTypeId;
                        m.MaterialTypeName = item.MaterialTypeName;
                        m.InventoryId = item.Id;
                        m.WarehouseId = item.WarehouseId;
                        Context.TStocktakingOrderDetails.Add(m);
                        Context.SaveChanges();
                    }
                    tran.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    throw ex;
                }
            }
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public bool DelStocktakingOrder(int ids)
        {
            try
            {
                //盘点单
                var del = Context.TStocktakingOrders.Find(ids);
                Context.TStocktakingOrders.Remove(del);
                //盘点单明细
                var slis = Context.TStocktakingOrderDetails.Where(x => x.OrderId == ids);
                if (slis.Any())
                {
                    Context.TStocktakingOrderDetails.RemoveRange(slis);
                }
                return Context.SaveChanges() > 0;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="wid"></param>
        /// <param name="aid"></param>
        /// <param name="name"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public List<TStocktakingDto> GetStocktakingOrders(string? StartTime, string? EndTime, string? OrderSn, int? StocktakingEndStatus, int pageIndex, int pageSize, out int totalCount)
        {
            try
            {
                var stocktakingOrdersList = Context.TStocktakingOrders.AsQueryable().ToList();
                var stocktakingOrderDetailList = Context.TStocktakingOrderDetails.AsQueryable().ToList();

                if (StartTime != null && EndTime != null)
                {
                    string StartdateString = StartTime;
                    DateTime Startdate = DateTime.Parse(StartdateString);
                    string EnddateString = EndTime;
                    DateTime Enddate = DateTime.Parse(EnddateString);
                    stocktakingOrdersList = stocktakingOrdersList.Where(x => x.CreateDate >= Startdate && x.CreateDate <= Enddate).ToList();

                }
                if (OrderSn != null)
                {
                    stocktakingOrdersList = stocktakingOrdersList.Where(x => x.OrderSn == OrderSn).ToList();
                }
                if (StocktakingEndStatus != 0)
                {
                    stocktakingOrderDetailList = stocktakingOrderDetailList.Where(x => x.StocktakingEndStatus == StocktakingEndStatus).ToList();
                }


                //totalCount = list.Count();
                //list = list.OrderByDescending(x => x.CreateDate).Skip((pageIndex - 1) * pageSize).Take(pageSize);
                List<TStocktakingDto> stocktakingDtos = new List<TStocktakingDto>();
                foreach (var item in stocktakingOrdersList)
                {
                    TStocktakingDto stocktakingDto = new TStocktakingDto();
                    stocktakingDto.Id = item.Id;
                    stocktakingDto.Name = item.Name;
                    stocktakingDto.OrderSn = item.OrderSn;
                    stocktakingDto.WarehouseId = item.WarehouseId;
                    stocktakingDto.WarehouseCode = item.WarehouseCode;
                    stocktakingDto.WarehouseName = item.WarehouseName;
                    stocktakingDto.AreaId = item.AreaId;
                    stocktakingDto.AreaCode = item.AreaCode;
                    stocktakingDto.AreaName = item.AreaName;
                    stocktakingDto.CompanyId = item.CompanyId;
                    stocktakingDto.CompanyName = item.CompanyName;
                    stocktakingDto.BillType = item.BillType;
                    stocktakingDto.Erpcode = item.Erpcode;
                    stocktakingDto.Status = item.Status;
                    stocktakingDto.MaterialId = item.MaterialId;
                    stocktakingDto.MaterialName = item.MaterialName;
                    stocktakingDto.BatchNo = item.BatchNo;
                    stocktakingDto.PlanType = item.PlanType;
                    stocktakingDto.StocktakeWay = item.StocktakeWay;
                    stocktakingDto.MaxLocationCount = item.MaxLocationCount;
                    stocktakingDto.Granularity = item.Granularity;
                    stocktakingDto.CellCount = item.CellCount;
                    stocktakingDto.FinishedCount = item.FinishedCount;
                    stocktakingDto.TouchTimes = item.TouchTimes;
                    stocktakingDto.TouchStartDate = item.TouchStartDate;
                    stocktakingDto.TouchEndDate = item.TouchEndDate;
                    stocktakingDto.LoopTimes = item.LoopTimes;
                    stocktakingDto.PriorityLevel = item.PriorityLevel;
                    stocktakingDto.ExecDate = item.ExecDate;
                    stocktakingDto.Remark = item.Remark;
                    stocktakingDto.CreateUserId = item.CreateUserId;
                    stocktakingDto.CreateUserName = item.CreateUserName;
                    stocktakingDto.CreateDate = item.CreateDate;
                    stocktakingDto.ModifyUserId = item.ModifyUserId;
                    stocktakingDto.ModifyUserName = item.ModifyUserName;
                    stocktakingDto.ModifyDate = item.ModifyDate;
                    stocktakingDto.SubmitDate = item.SubmitDate;
                    stocktakingDto.SubmitUserId = item.SubmitUserId;
                    stocktakingDto.SubmitUserName = item.SubmitUserName;
                    stocktakingDto.AuditDate = item.AuditDate;
                    stocktakingDto.AuditUserId = item.AuditUserId;
                    stocktakingDto.AuditUserName = item.AuditUserName;
                    //stocktakingDtos.Add( stocktakingDto );

                    foreach (var detail in stocktakingOrderDetailList.Where(x => x.OrderSn == item.OrderSn).ToList())
                    {
                        var materialList = Context.TMaterial.Where(x => x.Code == detail.MaterialCode).ToList();
                        if (materialList.Count<= 0)
                        {
                            continue;
                        }
                        TStocktakingChildList childDto = new TStocktakingChildList
                        {
                            Id = detail.Id,
                            OrderId = detail.OrderId,
                            OrderSn = detail.OrderSn,
                            CellId = detail.CellId,
                            CellCode = detail.CellCode,
                            LocationName = detail.LocationName,
                            CellDepthIndex = detail.CellDepthIndex,
                            PalletBarcode = detail.PalletBarcode,
                            ExpectedStationIds = detail.ExpectedStationIds,
                            Status = detail.Status,
                            CreateDate = detail.CreateDate,
                            MaterialId = detail.MaterialId,
                            MaterialCategoryName = detail.MaterialCategoryName,
                            MaterialCode = detail.MaterialCode,
                            MaterialName = detail.MaterialName,
                            HasSkuDetail = detail.HasSkuDetail,
                            Qty = stocktakingDto.Status == 4 ? detail.Qty : 0,
                            StocktakingQty = detail.StocktakingQty,
                            QualityState = detail.QualityState,
                            BatchNo = detail.BatchNo,
                            OwnerId = detail.OwnerId,
                            OwnerName = detail.OwnerName,
                            ExpireDate = detail.ExpireDate,
                            ProductDate = detail.ProductDate,
                            SupplierId = detail.SupplierId,
                            SupplierName = detail.SupplierName,
                            MaterialTypeId = detail.MaterialTypeId,
                            MaterialTypeName = detail.MaterialTypeName,
                            UnitName = materialList.First().UnitName == "" ? "" : materialList.First().UnitName,
                            ModelSpec = materialList.First().ModelSpec == "" ? "" : materialList.First().ModelSpec,
                            Step = detail.Step,
                            IsNeedMove = detail.IsNeedMove,
                            InventoryId = detail.InventoryId,
                            WarehouseId = detail.WarehouseId,
                            StocktakingEndStatus = detail.StocktakingEndStatus,

                        };
                        stocktakingDto.ChildList.Add(childDto);

                    }

                    stocktakingDtos.Add(stocktakingDto);
                }


                totalCount = stocktakingDtos.Count();
                stocktakingDtos = stocktakingDtos.OrderByDescending(x => x.CreateDate).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
                return stocktakingDtos;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 条件查询
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public TStocktakingOrderDto GetStocktakingOrder(int ids)
        {
            var tsobj = Context.TStocktakingOrders.Find(ids);
            TStocktakingOrderDto m = new TStocktakingOrderDto();
            if (tsobj != null)
            {

                m = Mapper.Map<TStocktakingOrderDto>(tsobj);

                var tsoobj = Context.TStocktakingOrderDetails.Where(x => x.OrderId == tsobj.Id);
                if (tsoobj.Any())
                {
                    List<TInventory> inventories = new List<TInventory>();
                    foreach (var item in tsoobj)
                    {
                        TInventory inventory = new TInventory();
                        inventory = Mapper.Map<TInventory>(item);
                        inventory.AvailableQty = item.Qty;
                        inventory.LocationId = item.CellId;
                        inventory.LocationCode = item.CellCode;
                        inventory.LocationDepth = (int)item.CellDepthIndex;
                        inventory.Id = (int)item.InventoryId;
                        inventories.Add(inventory);
                    }
                    //弃用
                    //var ilist = tsoobj.Select(x => new TInventory
                    //{  

                    //    PalletBarcode = x.PalletBarcode,
                    //    Status = x.Status,
                    //    MaterialId = x.MaterialId,
                    //    MaterialName = x.MaterialName,
                    //    MaterialCode = x.MaterialCode,
                    //    CreateDate = x.CreateDate,
                    //    HasSkuDetail = (bool)x.HasSkuDetail,
                    //    AvailableQty = x.Qty,
                    //    QualityState = x.QualityState,
                    //    BatchNo = x.BatchNo,
                    //    OwnerId = x.OwnerId,
                    //    OwnerName = x.OwnerName,
                    //    ExpireDate = x.ExpireDate,
                    //    ProductDate = x.ProductDate,
                    //    SupplierId= x.SupplierId,
                    //    SupplierName = x.SupplierName,
                    //    MaterialTypeId = x.MaterialTypeId,
                    //    MaterialTypeName = x.MaterialTypeName,
                    //    Id = (int)x.InventoryId,
                    //    WarehouseId= x.WarehouseId,
                    //});
                    m.Inventorys = inventories;
                }
            }
            return m;

        }

        /// <summary>
        /// 修改状态
        /// </summary>
        /// <param name="m"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public bool UpdTStocktakingOrderState(ReceivingOrderDetailDto m, out string message)
        {
            var robj = Context.TStocktakingOrders.Find(m.Id);

            if (robj != null)
            {
                switch (robj.Status)
                {
                    case 0:
                        var uobj = Context.TUsers.FirstOrDefault(x => x.Id == m.NowUserId);
                        if (uobj != null)
                        {
                            robj.SubmitUserId = m.NowUserId;
                            robj.SubmitUserName = m.NowUserRealName;
                            robj.SubmitDate = DateTime.Now;
                            robj.Remark = m.Remark;
                            //修改为：审核通过
                            robj.Status = 2;
                            Context.TStocktakingOrders.Update(robj);
                            break;
                        }
                        message = "工号有误！";
                        return false;

                    case 1:
                        break;

                    case 2:
                        robj.ModifyUserId = m.NowUserId;
                        robj.ModifyUserName = m.NowUserRealName;
                        robj.ModifyDate = DateTime.Now;
                        //修改为：作业中
                        robj.Status = 3;
                        Context.TStocktakingOrders.Update(robj);
                        break;

                    case 3:
                        robj.ModifyUserId = m.NowUserId;
                        robj.ModifyUserName = m.NowUserRealName;
                        robj.ModifyDate = DateTime.Now;
                        //修改为：已完成
                        robj.Status = 4;
                        Context.TStocktakingOrders.Update(robj);
                        break;

                    default:
                        break;
                }
                message = "修改成功！";
                return Context.SaveChanges() > 0;
            }
            message = "修改失败！";
            return false;
        }


        /// <summary>
        /// 获取状态为2的盘点单
        /// </summary>
        /// <returns></returns>
        public List<string> GetStocktakingList()
        {
            var list = Context.TStocktakingOrders.Where(x => x.Status == 2).OrderByDescending(x => x.CreateDate);
            List<string> orderSnList = new List<string>();
            foreach (var order in list)
            {
                string orderSn = order.OrderSn;
                orderSnList.Add(orderSn);
            }
            return orderSnList;
        }

        /// <summary>
        /// 根据单号查询详情
        /// </summary>
        /// <param name="orderSn"></param>
        /// <returns></returns>
        public List<TStocktakingOrderDetail> GetStocktakingDetailList(string orderSn)
        {
            var detailList = Context.TStocktakingOrderDetails.Where(x => x.OrderSn == orderSn).ToList();
            return detailList;
        }


        /// <summary>
        /// 根据库房库区查询库存表
        /// </summary>
        /// <param name="wid"></param>
        /// <param name="aid"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public List<TInventory> GetWhArList(int? id, string? name, string? startTime, string? endTime, int? type, int pageIndex, int pageSize, out int totalCount)
        {
            //var ilist = Context.TInventory.Where(x => x.WarehouseId == wid);
            var ilist = Context.TInventory.AsQueryable().ToList();
            if (ilist.Any())
            {
                if (id > 0)
                {
                    ilist = ilist.Where(x => x.MaterialCategoryId == id).ToList();
                }

                if (!string.IsNullOrEmpty(name))
                {
                    ilist = ilist.Where(x => x.MaterialName.Contains(name)).ToList();
                }
                if (startTime != null && endTime != null && type == 3)
                {
                    string StartdateString = startTime;
                    DateTime Startdate = DateTime.Parse(StartdateString);
                    string EnddateString = endTime;
                    DateTime Enddate = DateTime.Parse(EnddateString);
                    var ReceivingList = Context.TReceivingOrderDetails.Where(x => x.CreateDate >= Startdate && x.CreateDate <= Enddate).ToList();
                    var ShippingList = Context.TShippingLockCells.Where(x => x.CreateDate >= Startdate && x.CreateDate <= Enddate).ToList();
                    List<TInventory> inventories = new List<TInventory>();
                    if (ReceivingList.Count() > 0)
                    {
                        foreach (var item in ReceivingList)
                        {
                            var inventoryList = Context.TInventories.Where(x => x.MaterialCode == item.MaterialCode).ToList();
                            if (inventoryList.Count <= 0)
                            {
                                continue;
                            }
                            TInventory inventory = new TInventory();
                            inventory.Id = item.Id;
                            inventory.MaterialCategoryId = item.MaterialCategoryId;
                            inventory.MaterialCategoryCode = item.MaterialCategoryCode;
                            inventory.MaterialCategoryName = item.MaterialCategoryName;
                            inventory.MaterialId = item.MaterialId;
                            inventory.MaterialName = item.MaterialName;
                            inventory.MaterialCode = item.MaterialCode;
                            inventory.MaterialTypeCode = item.MaterialTypeCode;
                            inventory.MaterialTypeName = item.MaterialTypeName;
                            inventory.LocationId = inventoryList.FirstOrDefault().LocationId == 0 ? 0 : inventoryList.FirstOrDefault().LocationId;
                            inventory.LocationName = inventoryList.FirstOrDefault().LocationName == null ? "" : inventoryList.FirstOrDefault().LocationName;
                            inventory.AvailableQty = inventoryList.FirstOrDefault().AvailableQty == 0 ? 0 : inventoryList.FirstOrDefault().AvailableQty;
                            inventory.ModelSpec = item.ModelSpec;
                            inventory.UnitId = item.UnitId;
                            inventory.UnitName = item.UnitName;
                            inventory.QualityState = item.QualityState;
                            inventories.Add(inventory);

                        }
                    }
                    if (ShippingList.Count() > 0)
                    {
                        foreach (var shipping in ShippingList)
                        {
                            var inventoryList = Context.TInventories.Where(x => x.MaterialCode == shipping.MaterialCode).ToList();
                            if (inventoryList.Count <= 0)
                            {
                                continue;
                            }
                            TInventory inventory = new TInventory();
                            inventory.Id = shipping.Id;
                            inventory.MaterialCategoryId = shipping.MaterialCategoryId;
                            inventory.MaterialCategoryCode = shipping.MaterialCategoryCode;
                            inventory.MaterialCategoryName = shipping.MaterialCategoryName;
                            inventory.MaterialId = shipping.MaterialId;
                            inventory.MaterialName = shipping.MaterialName;
                            inventory.MaterialCode = shipping.MaterialCode;

                            inventory.LocationId = inventoryList.FirstOrDefault().LocationId == 0 ? 0 : inventoryList.FirstOrDefault().LocationId;
                            inventory.LocationName = inventoryList.FirstOrDefault().LocationName == null ? "" : inventoryList.FirstOrDefault().LocationName;
                            inventory.AvailableQty = inventoryList.FirstOrDefault().AvailableQty == 0 ? 0 : inventoryList.FirstOrDefault().AvailableQty;
                            inventory.ModelSpec = shipping.ModelSpec;
                            inventory.QualityState = shipping.QualityState;
                            inventories.Add(inventory);
                        }
                    }

                    inventories = inventories.Skip((pageIndex - 1) * pageSize).Take(pageSize).DistinctBy(d => d.MaterialCode).ToList();
                    totalCount = inventories.Count();
                    return inventories;
                }
                if (startTime != null && endTime != null && type == 4)
                {
                    string StartdateString = startTime;
                    DateTime Startdate = DateTime.Parse(StartdateString);
                    string EnddateString = endTime;
                    DateTime Enddate = DateTime.Parse(EnddateString);
                    List<TInventory> inventories = new List<TInventory>();
                    var TransferBinOrder = Context.TTransferBinOrders.Where(x => x.CreateDate >= Startdate && x.CreateDate <= Enddate).ToList();
                    foreach (var items in TransferBinOrder)
                    {

                        var TransferBinList = Context.TTransferBinOrderDetails.Where(x => x.OrderSn == items.OrderSn).ToList();
                        if (TransferBinList.Count() > 0)
                        {
                            foreach (var item in TransferBinList)
                            {
                                var inventoryList = Context.TInventories.Where(x => x.MaterialCode == item.MaterialCode).ToList();
                                if (inventoryList.Count <= 0)
                                {
                                    continue;
                                }
                                TInventory inventory = new TInventory();
                                inventory.Id = item.Id;
                                inventory.MaterialCategoryId = item.MaterialCategoryId;
                                inventory.MaterialCategoryName = item.MaterialCategoryName;
                                inventory.MaterialId = item.MaterialId;
                                inventory.MaterialName = item.MaterialName;
                                inventory.MaterialCode = item.MaterialCode;
                                inventory.LocationId = inventoryList.FirstOrDefault().LocationId == 0 ? 0 : inventoryList.FirstOrDefault().LocationId;
                                inventory.LocationName = inventoryList.FirstOrDefault().LocationName == null ? "" : inventoryList.FirstOrDefault().LocationName;
                                inventory.AvailableQty = inventoryList.FirstOrDefault().AvailableQty == 0 ? 0 : inventoryList.FirstOrDefault().AvailableQty;
                                inventory.ModelSpec = item.ModelSpec;
                                inventory.QualityState = item.QualityState;
                                inventories.Add(inventory);
                            }
                        }
                    }

                    inventories = inventories.Skip((pageIndex - 1) * pageSize).Take(pageSize).DistinctBy(d => d.MaterialCode).ToList();
                    totalCount = inventories.Count();
                    return inventories;
                }
                ilist = ilist.Skip((pageIndex - 1) * pageSize).Take(pageSize).DistinctBy(d => d.MaterialCode).ToList();
                totalCount = ilist.Count();
                return ilist;
            }
            totalCount = 0;
            return ilist.ToList();
        }

        /// <summary>
        /// 往sqlite数据库插入数据
        /// </summary>
        /// <param name="m"></param>
        /// <returns></returns>
        public bool AddSqliteDetail(TStocktakingOrderDto m, out string message)
        {
            using var tran = Context.Database.BeginTransaction();
            try
            {
                message = "";
                //创建表（如果不存在）
                bool aa = sqlite.Database.EnsureCreated();
                // 设置连接字符串
                string connectionString = "Data Source=.\\wwwroot\\file\\chat.db";

                // 创建连接对象
                using (var connection = new SqliteConnection(connectionString))
                {
                    // 打开连接
                    connection.Open();

                    // 创建SQL命令对象
                    using (var command = connection.CreateCommand())
                    {
                        // 设置清空缓存的SQL语句
                        command.CommandText = "PRAGMA cache_size=0;";

                        // 执行SQL命令
                        command.ExecuteNonQuery();

                        Console.WriteLine("Cache cleared.");
                    }

                    // 关闭连接
                    connection.Close();
                }
                List<TStocktakingChild> childrenss = new List<TStocktakingChild>();
                foreach (var item in m.stocktakingOrderDetails)
                {
                    TStocktakingChild stocktakingChild = new TStocktakingChild();
                    stocktakingChild.Id = item.Id;
                    stocktakingChild.CellId = item.CellId;
                    stocktakingChild.CellCode = item.CellCode;
                    stocktakingChild.LocationName = item.LocationName;
                    stocktakingChild.CreateDate = item.CreateDate;
                    stocktakingChild.MaterialId = item.MaterialId;
                    stocktakingChild.MaterialCategoryName = item.MaterialCategoryName;
                    stocktakingChild.MaterialCode = item.MaterialCode;
                    stocktakingChild.MaterialName = item.MaterialName;
                    stocktakingChild.Qty = Context.TInventories.First(x => x.MaterialCode == item.MaterialCode).AvailableQty;
                    stocktakingChild.MaterialTypeId = item.MaterialTypeId;
                    stocktakingChild.MaterialTypeName = item.MaterialTypeName;
                    stocktakingChild.WarehouseId = item.WarehouseId;
                    childrenss.Add(stocktakingChild);
                }

                var Json = JsonConvert.SerializeObject(childrenss);
                string OrderSnByError = "";
                var TStocktakingModes = new TStocktakingModes();
                //Id = m.Id,
                //OrderId = m.stocktakingOrderDetails.FirstOrDefault(x => x.OrderId == m.Id).Id,
                TStocktakingModes.OrderSn = m.OrderSn;
                OrderSnByError = m.OrderSn;
                TStocktakingModes.JsonData = Json;
                sqlite.TStocktakingModes.Add(TStocktakingModes);

                if (sqlite.SaveChanges() <= 0)
                {
                    tran.Rollback();
                    message = "盘点信息添加失败! 单号:" + OrderSnByError;
                    return false;
                }
                tran.Commit();
                message = "盘点数据添加成功";
                return true;
            }
            catch
            {
                tran.Rollback();
                message = "盘点添加数据失败!";
                return false;
            }

        }

        /// <summary>
        /// 读取sqlite数据库数据
        /// </summary>
        /// <returns></returns>
        public bool SubmitTStocktaking(out string message)
        {
            using var tran = Context.Database.BeginTransaction();
            try
            {
                message = "";
                //创建表（如果不存在）
                sqlite.Database.EnsureCreated();


                var list = sqlite.SubmitStocktaking.AsQueryable();

                if (list == null || !list.Any())
                {
                    message = "盘点单未查询到数据";
                    return false;
                }
                string OrderSn = "";//初始单号
                string OrderSnDouble = "";//当前单号
                string OrderSnThree = "";//上个单号
                List<TStocktakingOrderDto> dto = new List<TStocktakingOrderDto>();

                foreach (var item in list)
                {
                    TStocktakingOrderDto stocktakingOrderDto = new TStocktakingOrderDto();
                    TStocktakingOrderDetail stocktakingOrderDetail = new TStocktakingOrderDetail();
                    stocktakingOrderDto.OrderSn = item.OrderSn;
                    OrderSn = list.First().OrderSn;
                    OrderSnDouble = item.OrderSn;
                    stocktakingOrderDetail.MaterialCategoryName = item.MaterialCategoryName;
                    stocktakingOrderDetail.MaterialName = item.MaterialName;
                    stocktakingOrderDetail.MaterialCode = item.MaterialCode;
                    stocktakingOrderDetail.CellCode = item.CellCode;
                    stocktakingOrderDetail.LocationName = item.LocationName;
                    stocktakingOrderDetail.Qty = item.Qty;
                    stocktakingOrderDetail.StocktakingQty = item.StocktakingQty;
                    stocktakingOrderDetail.CreateDate = DateTime.Now;
                    if (item.StocktakingQty - item.Qty > 0)
                    {
                        stocktakingOrderDetail.StocktakingEndStatus = 1;
                    }
                    else if (item.StocktakingQty - item.Qty == 0)
                    {
                        stocktakingOrderDetail.StocktakingEndStatus = 2;
                    }
                    else
                    {
                        stocktakingOrderDetail.StocktakingEndStatus = 3;
                    }
                    stocktakingOrderDto.stocktakingOrderDetails.Add(stocktakingOrderDetail);
                    dto.Add(stocktakingOrderDto);

                    if (OrderSn == OrderSnDouble)
                    {
                        if (OrderSnThree != "" && OrderSnDouble == OrderSnThree)
                        {
                            var SubmitStocktakngList = sqlite.SubmitStocktaking.Where(x => x.OrderSn == item.OrderSn).ToList();
                            sqlite.SubmitStocktaking.RemoveRange(SubmitStocktakngList);
                            var TstocktakingModesList = sqlite.TStocktakingModes.Where(x => x.OrderSn == item.OrderSn).ToList();
                            sqlite.TStocktakingModes.RemoveRange(TstocktakingModesList);
                            if (sqlite.SaveChanges() <= 0)
                            {
                                tran.Rollback();
                                message = "盘点数据更新失败!";
                                return false;
                            }
                        }

                    }
                    else
                    {
                        if (OrderSnDouble != OrderSnThree)
                        {
                            var SubmitStocktakngList = sqlite.SubmitStocktaking.Where(x => x.OrderSn == item.OrderSn).ToList();
                            sqlite.SubmitStocktaking.RemoveRange(SubmitStocktakngList);
                            var TstocktakingModesList = sqlite.TStocktakingModes.Where(x => x.OrderSn == item.OrderSn).ToList();
                            sqlite.TStocktakingModes.RemoveRange(TstocktakingModesList);
                            if (sqlite.SaveChanges() <= 0)
                            {
                                tran.Rollback();
                                message = "盘点数据更新失败!";
                                return false;
                            }
                        }
                    }
                    OrderSnThree = item.OrderSn;

                }
                List<TStocktakeResultRecord> statusDto = new List<TStocktakeResultRecord>();

                foreach (var item in dto)
                {
                    TStocktakeResultRecord stocktakeResultRecord = new TStocktakeResultRecord();

                    foreach (var itemDto in item.stocktakingOrderDetails)
                    {
                        TStocktakingOrderDetail detail = Context.TStocktakingOrderDetails.Where(x => x.OrderSn == item.OrderSn && x.MaterialCode == itemDto.MaterialCode).First();
                        detail.OrderSn = item.OrderSn;
                        detail.StocktakingQty = itemDto.StocktakingQty;
                        //StocktakingStatusDto st = new StocktakingStatusDto();
                        if (itemDto.Qty > itemDto.StocktakingQty)
                        {
                            stocktakeResultRecord.Status = 3;
                            detail.StocktakingEndStatus = 3;
                        }
                        else if (itemDto.Qty == itemDto.StocktakingQty)
                        {
                            stocktakeResultRecord.Status = 2;
                            detail.StocktakingEndStatus = 2;
                        }
                        else if (itemDto.Qty < itemDto.StocktakingQty)
                        {
                            stocktakeResultRecord.Status = 1;
                            detail.StocktakingEndStatus = 1;
                        }

                        //stocktakeResultRecord.MaterialCategoryName = itemDto.MaterialCategoryName;
                        stocktakeResultRecord.OrderSn = item.OrderSn;
                        stocktakeResultRecord.MaterialName = itemDto.MaterialName;
                        stocktakeResultRecord.MaterialBarcode = itemDto.MaterialCode;
                        stocktakeResultRecord.CreateDate = itemDto.CreateDate;
                        stocktakeResultRecord.OriginalQty = itemDto.Qty;
                        stocktakeResultRecord.CountQty = itemDto.StocktakingQty;
                        stocktakeResultRecord.CellCode = itemDto.CellCode;


                        statusDto.Add(stocktakeResultRecord);

                    }
                    Context.TStocktakeResultRecords.AddRange(statusDto);


                    var TStocktakinglist = Context.TStocktakingOrders.FirstOrDefault(x => x.OrderSn == item.OrderSn);
                    if (TStocktakinglist != null)
                    {
                        TStocktakinglist.Status = 4;
                    }

                }
                if (Context.SaveChanges() >= 0)
                {
                    tran.Commit();
                    message = "盘点信息更新成功！";
                    return true;
                }
                tran.Rollback();
                message = "盘点数据更新失败";
                return false;
            }
            catch
            {
                tran.Rollback();
                message = "盘点数据更新失败!";
                return false;
            }

        }


        /// <summary>
        /// 提交（有网）
        /// </summary>
        /// <param name="m"></param>
        /// <param name="message"></param>
        /// <returns></returns>
        public bool UpTSocktakingStatus(TStocktakingOrderDetail m, out string message)
        {
            message = "";
            using var tran = Context.Database.BeginTransaction();
            try
            {
                List<TStocktakingOrderDto> dto = new List<TStocktakingOrderDto>();

                TStocktakingOrderDto stocktakingOrderDto = new TStocktakingOrderDto();
                stocktakingOrderDto.OrderSn = m.OrderSn;
                stocktakingOrderDto.stocktakingOrderDetails.Add(m);
                dto.Add(stocktakingOrderDto);


                List<TStocktakeResultRecord> statusDto = new List<TStocktakeResultRecord>();

                foreach (var item in dto)
                {
                    TStocktakeResultRecord stocktakeResultRecord = new TStocktakeResultRecord();

                    foreach (var itemDto in item.stocktakingOrderDetails)
                    {
                        var inventoryList = Context.TInventories.Where(x => x.LocationName == itemDto.LocationName && x.MaterialCode == itemDto.MaterialCode).ToList();
                        TStocktakingOrderDetail detail = Context.TStocktakingOrderDetails.Where(x => x.OrderSn == item.OrderSn && x.MaterialCode == itemDto.MaterialCode).First();
                        detail.OrderSn = item.OrderSn;
                        detail.StocktakingQty = itemDto.StocktakingQty;
                        //StocktakingStatusDto st = new StocktakingStatusDto();
                        if(inventoryList.Count <= 0)
                        {
                            continue;
                        }
                        if (inventoryList.FirstOrDefault().AvailableQty > itemDto.StocktakingQty)
                        {
                            stocktakeResultRecord.Status = 3;
                            detail.StocktakingEndStatus = 3;
                        }
                        else if (inventoryList.FirstOrDefault().AvailableQty == itemDto.StocktakingQty)
                        {
                            stocktakeResultRecord.Status = 2;
                            detail.StocktakingEndStatus = 2;
                        }
                        else if (inventoryList.FirstOrDefault().AvailableQty < itemDto.StocktakingQty)
                        {
                            stocktakeResultRecord.Status = 1;
                            detail.StocktakingEndStatus = 1;
                        }

                        //stocktakeResultRecord.MaterialCategoryName = itemDto.MaterialCategoryName;
                        stocktakeResultRecord.OrderSn = item.OrderSn;
                        stocktakeResultRecord.MaterialName = itemDto.MaterialName;
                        stocktakeResultRecord.MaterialBarcode = itemDto.MaterialCode;
                        stocktakeResultRecord.CreateDate = itemDto.CreateDate;
                        stocktakeResultRecord.OriginalQty = inventoryList.FirstOrDefault().AvailableQty;
                        stocktakeResultRecord.CountQty = itemDto.StocktakingQty;
                        stocktakeResultRecord.CellCode = inventoryList.FirstOrDefault().LocationCode;
                        stocktakeResultRecord.CreateDate = DateTime.Now;


                        statusDto.Add(stocktakeResultRecord);

                    }
                    Context.TStocktakeResultRecords.AddRange(statusDto);


                    var TStocktakinglist = Context.TStocktakingOrders.FirstOrDefault(x => x.OrderSn == item.OrderSn);
                    if (TStocktakinglist != null)
                    {
                        TStocktakinglist.Status = 4;
                    }

                }
                if (Context.SaveChanges() >= 0)
                {
                    tran.Commit();
                    message = "盘点信息更新成功！";
                    return true;
                }
                tran.Rollback();
                message = "盘点数据更新失败";
                return false;
            }
            catch
            {
                tran.Rollback();
                message = "盘点数据更新失败!";
                return false;
            }

        }




        ///<summary>
        ///根据OrderSn获取数据
        /// </summary>
        /// <param name="OrderSn"></param>
        /// <returns></returns>
        public List<TStocktakingOrderDto> GetStocktakingByOrderSn(string OrderSn)
        {
            if (OrderSn == null)
            {
                return null;
            }

            var detailsList = Context.TStocktakingOrderDetails.Where(x => x.OrderSn == OrderSn).ToList();
            var result = new List<TStocktakingOrderDto>();

            foreach (var detail in detailsList)
            {
                var existingDto = result.FirstOrDefault(dto => dto.OrderSn == detail.OrderSn);
                if (existingDto == null)
                {
                    existingDto = new TStocktakingOrderDto { OrderSn = detail.OrderSn };
                    result.Add(existingDto);
                }
                existingDto.stocktakingOrderDetails.Add(detail);
            }

            return result;
        }


        public List<TStocktakingOrderDetailDto> GetTStocktakingOrderDetails(string OrderSn)
        {
            List<TStocktakingOrderDetailDto> list = new List<TStocktakingOrderDetailDto> { };

            var stocktakingDetailList = Context.TStocktakingOrderDetails.Where(x => x.OrderSn == OrderSn).ToList();
            List<TStocktakingDtoChildList> stocktakingDtoChildListList = new List<TStocktakingDtoChildList>();
            foreach (var item in stocktakingDetailList)
            {
                TStocktakingDtoChildList stocktakingDtoChildList = new TStocktakingDtoChildList();
                stocktakingDtoChildList.Id = item.Id;

                stocktakingDtoChildList.OrderId = item.OrderId;
                stocktakingDtoChildList.OrderSn = item.OrderSn;
                stocktakingDtoChildList.CellId = item.CellId;
                stocktakingDtoChildList.CellCode = item.CellCode;
                stocktakingDtoChildList.LocationName = item.LocationName;
                stocktakingDtoChildList.CellDepthIndex = item.CellDepthIndex;
                stocktakingDtoChildList.PalletBarcode = item.PalletBarcode;
                stocktakingDtoChildList.ExpectedStationIds = item.ExpectedStationIds;
                stocktakingDtoChildList.Status = item.Status;
                stocktakingDtoChildList.CreateDate = item.CreateDate;
                stocktakingDtoChildList.MaterialId = item.MaterialId;
                stocktakingDtoChildList.MaterialCategoryName = item.MaterialCategoryName;
                stocktakingDtoChildList.MaterialCode = item.MaterialCode;
                stocktakingDtoChildList.MaterialName = item.MaterialName;
                stocktakingDtoChildList.HasSkuDetail = item.HasSkuDetail;
                stocktakingDtoChildList.Qty = item.Qty;
                stocktakingDtoChildList.AvailableQty = item.Qty;
                stocktakingDtoChildList.ModelSpec = Context.TMaterial.First(x => x.Code == item.MaterialCode).ModelSpec;
                stocktakingDtoChildList.UnitName = Context.TMaterial.First(x => x.Code == item.MaterialCode).UnitName;
                stocktakingDtoChildList.StocktakingQty = item.StocktakingQty;
                stocktakingDtoChildList.QualityState = item.QualityState;
                stocktakingDtoChildList.BatchNo = item.BatchNo;
                stocktakingDtoChildList.OwnerId = item.OwnerId;
                stocktakingDtoChildList.OwnerName = item.OwnerName;
                stocktakingDtoChildList.ExpireDate = item.ExpireDate;
                stocktakingDtoChildList.ProductDate = item.ProductDate;
                stocktakingDtoChildList.SupplierId = item.SupplierId;
                stocktakingDtoChildList.SupplierName = item.SupplierName;
                stocktakingDtoChildList.MaterialTypeId = item.MaterialTypeId;
                stocktakingDtoChildList.MaterialTypeName = item.MaterialTypeName;
                stocktakingDtoChildList.Step = item.Step;
                stocktakingDtoChildList.IsNeedMove = item.IsNeedMove;
                stocktakingDtoChildList.InventoryId = item.InventoryId;
                stocktakingDtoChildList.WarehouseId = item.WarehouseId;
                stocktakingDtoChildList.StocktakingEndStatus = item.StocktakingEndStatus;
                stocktakingDtoChildListList.Add(stocktakingDtoChildList);
            }

            var stocktakingList = Context.TStocktakingOrders.Where(x => x.OrderSn == OrderSn).ToList();

            foreach (var item in stocktakingList)
            {
                TStocktakingOrderDetailDto dto = new TStocktakingOrderDetailDto { };
                dto.Id = item.Id;
                dto.Name = item.Name;
                dto.OrderSn = item.OrderSn;
                dto.WarehouseId = item.WarehouseId;
                dto.WarehouseName = item.WarehouseName;
                dto.WarehouseCode = item.WarehouseCode;
                dto.CompanyId = item.CompanyId;
                dto.CompanyName = item.CompanyName;
                dto.BillType = item.BillType;
                dto.Erpcode = item.Erpcode;
                dto.Status = item.Status;
                dto.MaterialId = item.MaterialId;
                dto.MaterialName = item.MaterialName;
                dto.BatchNo = item.BatchNo;
                dto.PlanType = item.PlanType;
                dto.StocktakeWay = item.StocktakeWay;
                dto.MaxLocationCount = item.MaxLocationCount;
                dto.Granularity = item.Granularity;
                dto.CellCount = item.CellCount;
                dto.FinishedCount = item.FinishedCount;
                dto.ExecDate = item.ExecDate;
                dto.Remark = item.Remark;
                dto.CreateUserId = item.CreateUserId;
                dto.CreateUserName = item.CreateUserName;
                dto.CreateDate = item.CreateDate;
                dto.ModifyUserId = item.ModifyUserId;
                dto.ModifyUserName = item.ModifyUserName;
                dto.ModifyDate = item.ModifyDate;
                dto.SubmitDate = item.SubmitDate;
                dto.SubmitUserId = item.SubmitUserId;
                dto.SubmitUserName = item.SubmitUserName;
                dto.AuditDate = item.AuditDate;
                dto.AuditUserId = item.AuditUserId;
                dto.AuditUserName = item.AuditUserName;
                dto.Reason = item.ReasonName;
                dto.ChildList = stocktakingDtoChildListList;
                list.Add(dto);
            }
            return list.ToList();
        }

        /// <summary>
        /// 修改盘点凭证
        /// </summary>
        /// <param name="OrderSn"></param>
        /// <param name="MaterialCode"></param>
        /// <param name="FileUrl"></param>
        /// <returns></returns>
        public bool UpdStocktakingFileUrl(string OrderSn, string MaterialCode, string FileUrl)
        {
            var StocktakingList = Context.TStocktakingOrderDetails.Where(x => x.OrderSn == OrderSn && x.MaterialCode == MaterialCode).First();
            StocktakingList.FileUrl = FileUrl;
            Context.TStocktakingOrderDetails.Update(StocktakingList);
            if (Context.SaveChanges() > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 查看盘点凭证
        /// </summary>
        /// <param name="OrderSn"></param>
        /// <param name="MaterialCode"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public string GetStocktakingFileUrl(string OrderSn, string MaterialCode)
        {
            var StocktakingList = Context.TStocktakingOrderDetails.Where(x => x.OrderSn == OrderSn && x.MaterialCode == MaterialCode).First();
            return StocktakingList.FileUrl;
        }
    }
}
